package com.dhcc.DRGsHosp.application.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.QuotaApplication;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**
 * @title		:QuotaApplicationImpl.java
 * @description	:
 * @author		:lileizheng
 * @date		:2016年6月12日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class QuotaApplicationImpl implements QuotaApplication {
	
	private EntityRepository getRepository() {
		EntityRepository repository = null;
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj; 

	/**
	 * 
	 *  @Description：获取指标配置
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#getQuotas(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public List<Object[]> getQuotas(String roleCode, String userCode, String pageCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
		sql.append(" case when c.quota_code is not null then 1 else 0 end releate , b.is_link \n ");
		sql.append(" from drgs.t_quota a  \n ");
		sql.append(" left join drgs.t_quotaselect b  \n ");
		sql.append(" on a.code=b.quota_code  \n ");
		sql.append(" left join drgs.t_quotaselect c  \n ");
		sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
		sql.append(" where 1=1 \n ");
		sql.append(" and user_code='"+userCode+"' ");
		sql.append(" and role_code='"+roleCode+"' ");
		sql.append(" and page_code='"+pageCode+"' ");
		sql.append(" order by a.id");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
		if(list.size()<=0){
			if(StringUtils.hasLength(pageCode) && pageCode.indexOf("-co")>0){
				pageCode = pageCode.replace("-co", "");
				sql = new StringBuilder();
				sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
				sql.append(" case when c.quota_code is not null then 1 else 0 end releate , b.is_link \n ");
				sql.append(" from drgs.t_quota a  \n ");
				sql.append(" left join drgs.t_quotaselect b  \n ");
				sql.append(" on a.code=b.quota_code  \n ");
				sql.append(" left join drgs.t_quotaselect c  \n ");
				sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
				sql.append(" where 1=1 \n ");
				sql.append(" and user_code='"+userCode+"' ");
				sql.append(" and role_code='"+roleCode+"' ");
				sql.append(" and page_code='"+pageCode+"' ");
				sql.append(" order by a.id");
				list = getRepository().createSqlQuery(sql.toString()).list();
				if(list.size()>0){
					return list;
				}
			}
			sql = new StringBuilder();
			sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
			sql.append(" case when c.quota_code is not null then 1 else 0 end releate , b.is_link \n ");
			sql.append(" from drgs.t_quota a  \n ");
			sql.append(" left join drgs.t_quotaselect b  \n ");
			sql.append(" on a.code=b.quota_code  \n ");
			sql.append(" left join drgs.t_quotaselect c  \n ");
			sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
			sql.append(" where 1=1 \n ");
			sql.append(" and role_code='"+roleCode+"' ");
			sql.append(" and user_code is null ");
			sql.append(" and page_code is null ");
			sql.append(" order by a.id");
			list = getRepository().createSqlQuery(sql.toString()).list();
			if(list.size()<=0){
				sql = new StringBuilder();
				sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
				sql.append(" case when c.quota_code is not null then 1 else 0 end releate , b.is_link \n ");
				sql.append(" from drgs.t_quota a  \n ");
				sql.append(" left join drgs.t_quotaselect b  \n ");
				sql.append(" on a.code=b.quota_code  \n ");
				sql.append(" left join drgs.t_quotaselect c  \n ");
				sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
				sql.append(" where 1=1 \n ");
				sql.append(" and role_code is null ");
				sql.append(" and user_code is null ");
				sql.append(" and page_code is null ");
				sql.append(" order by a.id");
				list = getRepository().createSqlQuery(sql.toString()).list();
			}
		}else{
			sql = new StringBuilder();
			sql.append(" select a.code, a.unit, a.id, a.pid, a.text, 0, a.open, a.q_type, a.releate from ( \n ");
			sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
			sql.append(" case when c.quota_code is not null then 1 else 0 end releate , b.is_link \n ");
			sql.append(" from drgs.t_quota a  \n ");
			sql.append(" left join drgs.t_quotaselect b  \n ");
			sql.append(" on a.code=b.quota_code  \n ");
			sql.append(" left join drgs.t_quotaselect c  \n ");
			sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
			sql.append(" where 1=1  \n ");
			sql.append(" and role_code='"+roleCode+"' \n ");
			sql.append(" and user_code is null  \n ");
			sql.append(" and page_code is null  \n ");
			sql.append(" order by a.id \n ");
			sql.append(" ) a left join ( \n ");
			sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type, \n ");
			sql.append(" case when c.quota_code is not null then 1 else 0 end releate  \n ");
			sql.append(" from drgs.t_quota a  \n ");
			sql.append(" left join drgs.t_quotaselect b  \n ");
			sql.append(" on a.code=b.quota_code  \n ");
			sql.append(" left join drgs.t_quotaselect c  \n ");
			sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
			sql.append(" where 1=1  \n ");
			sql.append(" and role_code='"+roleCode+"' \n ");
			sql.append(" and user_code='"+userCode+"' \n ");
			sql.append(" and page_code='"+pageCode+"' \n ");
			sql.append(" order by a.id \n ");
			sql.append(" ) b on a.code=b.code \n ");
			sql.append(" where b.code is null \n ");
			List<Object[]> listt = getRepository().createSqlQuery(sql.toString()).list();
			list.addAll(listt);
		}
		return list;
	}
/**
 * 
 *  @Description：保存指标配置信息
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#saveQuotas(java.util.Map)
 */
	@Override
	public void saveQuotas(Map<String, Object> json) {
		try {
			String id = json.get("id") != null ? json.get("id").toString() : "";
			String pid = json.get("pid") != null ? json.get("pid").toString() : "";
			String code = json.get("code") != null ? json.get("code").toString() : "";
			String roleCode = json.get("roleCode") != null ? json.get("roleCode").toString() : "";
			String userCode = json.get("userCode") != null ? json.get("userCode").toString() : "";
			String pageCode = json.get("pageCode") != null ? json.get("pageCode").toString() : "";
			String checked = json.get("checked") != null ? ((boolean) json.get("checked") ? "1" : "0") : "";
			//管理员配置的时候默认全部选中
			if(pageCode.isEmpty()) {
				checked ="1";
			}
			json.put("checked", checked);
			String open = json.get("open") != null ? ((boolean) json.get("open") ? "1" : "0") : "";
			json.put("open", open);
			String priId = UUID.randomUUID().toString();
			json.put("priId", priId);
			Map<String, Object> param = new HashMap<>();
			param.put("id", id);
			param.put("pid", pid);
			param.put("code", code);
			param.put("roleCode", roleCode);
			param.put("pageCode", pageCode);
			String sql = "select t.checked,t.open from drgs.t_quota t where t.id=:id and pid=:pid and role_code=:roleCode and page_code=:pageCode and code=:code ";
			List<Object[]> list = getRepository().createSqlQuery(sql).setParameters(param).list();
			if (list.size() > 0) {
				// 选中和是否打开有一个不一样，则进行更新
				if (!checked.equals(list.get(0)[0].toString()) || !open.equals(list.get(0)[1].toString())) {
					param = new HashMap<>();
					param.put("id", id);
					param.put("pid", pid);
					param.put("code", code);
					param.put("roleCode", roleCode);
					param.put("userCode", userCode);
					param.put("pageCode", pageCode);
					param.put("checked", checked);
					param.put("open", open);

					if (StringUtils.hasLength(roleCode) && !StringUtils.hasLength(userCode) && !StringUtils.hasLength(pageCode)) {
						sql = "update drgs.t_quota t set checked=:checked, open=:open, t.id=:id, pid=:pid where role_code=:roleCode and code=:code ";
					} else {
						sql = "update drgs.t_quota t set checked=:checked, open=:open where t.id=:id and pid=:pid and role_code=:roleCode  and page_code=:pageCode and code=:code ";
					}
					getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
				}
			} else {
				if (pageCode.isEmpty()) {
					param = new HashMap<>();
					param.put("id", id);
					param.put("pid", pid);
					param.put("code", code);
					param.put("roleCode", roleCode);
					param.put("userCode", userCode);
					param.put("pageCode", pageCode);
					param.put("checked", checked);
					param.put("priId",  UUID.randomUUID().toString());
					param.put("open", open);
					sql = "insert into drgs.t_quota values(:id, :pid, :code, :roleCode, :userCode, :pageCode, :checked, :priId, :open) ";
					getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
					// 为改角色下当前所有存在的jsp页面都添加上次质保配置
					sql = "SELECT DISTINCT t.page_code FROM drgs.t_quota t WHERE t.role_code='" + roleCode + "' AND t.page_code IS NOT NULL";
					List<String> liststr = jdbcTemplateObj.queryForList(sql, String.class);
					if (!liststr.isEmpty()) {
						for (String pagecode : liststr) {
							param.put("pageCode", pagecode);
							param.put("priId",  UUID.randomUUID().toString());
							sql = "insert into drgs.t_quota values(:id, :pid, :code, :roleCode, :userCode, :pageCode, :checked, :priId, :open) ";
							getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
						}
					}
				} else {// 基本不走
					param = new HashMap<>();
					param.put("id", id);
					param.put("pid", pid);
					param.put("code", code);
					param.put("roleCode", roleCode);
					param.put("userCode", userCode);
					param.put("pageCode", pageCode);
					param.put("checked", checked);
					param.put("priId",  UUID.randomUUID().toString());
					param.put("open", open);
					sql = "insert into drgs.t_quota values(:id, :pid, :code, :roleCode, :userCode, :pageCode, :checked, :priId, :open) ";
					getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
				}
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 
	 *  @Description：清空角色配置的菜单
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#emptyQuotaSetting(java.lang.String, java.lang.String)
	 */
	@Override
	public void emptyQuotaSetting(String userCode, String roleCode){
		String sql = " delete from drgs.t_quota t where 1=1 ";
		if(StringUtils.hasLength(roleCode)){
			sql += " and role_code='"+roleCode+"' ";
		}else{
			sql += " and role_code is null ";
		}
		getRepository().createSqlQuery(sql).executeUpdate();
	}
/**
 * 
 *  @Description：获取指标列表
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#getQuotaList(java.lang.String)
 */
	@Override
	public List<Map<String,Object>> getQuotaList(String quotaType) {
		String sql = "select quota_code, text,mark, type, unit,quota_type,quota_type_dtl,is_quota,QUOTA_IS_PAGES  from drgs.t_quotaselect where 1=1 ";
		if(StringUtils.hasLength(quotaType)){
			sql += "and IS_QUOTA=1";//查询指标，排除关联类指标
		}		
		return jdbcTemplateObj.queryForList(sql);
	}

	@Override
	public List<Map<String,Object>> getQuotaByCode(String quotaCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select quota_code, text,type, unit, select_sql, q_type,IS_LINK,QUOTA_TYPE,QUOTA_TYPE_DTL,IS_QUOTA, QUOTA_IS_PAGES,MARK from DRGs.t_quotaselect where quota_code='"+quotaCode+"' ");
		List<Map<String,Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}
	
	@Override
	public List<Object[]> getQuotaByDesc(String quotaDesc) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select quota_code, text, type, unit, select_sql, q_type from DRGs.t_quotaselect where text='"+quotaDesc+"' ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public Object saveQuota(Map<String, Object> json) {
		String quotaCode = json.get("quotaCode")!=null?json.get("quotaCode").toString():"";
		String quotaName = json.get("quotaName")!=null?json.get("quotaName").toString():"";
		String quotaType = json.get("quotaType")!=null?json.get("quotaType").toString():"";
		String quotaTypeDtl=json.get("quotaTypeDtl")!=null?json.get("quotaTypeDtl").toString():"";
		String qType = json.get("qType")!=null?json.get("qType").toString():"";
		String unit = json.get("unit")!=null?json.get("unit").toString():"";
		String isLink=json.get("isLink")!=null?json.get("isLink").toString():"";
		String isQuota=json.get("isQuota")!=null?json.get("isQuota").toString():"";
		String sql = json.get("sql")!=null?json.get("sql").toString():"";
		String mark = json.get("mark")!=null?json.get("mark").toString():"";
		String type = json.get("type")!=null?json.get("type").toString():"";
		String quota_is_pages = json.get("QUOTA_IS_PAGES")!=null?json.get("QUOTA_IS_PAGES").toString():"";
		
		String sqlStr = "select 1 from drgs.t_quotaselect t where t.quota_code='"+quotaCode+"' ";
		List<Object[]> list = getRepository().createSqlQuery(sqlStr).list();
		Map<String,Object> map=new HashMap<>();
		if(list.size()>0){
			Map<String, Object> param = new HashMap<>();
			param.put("quotaCode", quotaCode);
			param.put("quotaName", quotaName);
			param.put("quotaType", quotaType);
			param.put("quotaTypeDtl", quotaTypeDtl);
			param.put("qType", qType);
			param.put("unit", unit);
			param.put("isLink",isLink);
			param.put("isQuota", isQuota);
			param.put("sql", sql);
			param.put("type", type);
			param.put("mark", mark);
			param.put("quota_is_pages", quota_is_pages);
			
			sqlStr = "update drgs.t_quotaselect set text=:quotaName, type=:type,QUOTA_TYPE=:quotaType,QUOTA_TYPE_DTL=:quotaTypeDtl, q_type=:qType, unit=:unit,IS_LINK=:isLink,IS_QUOTA=:isQuota, select_sql=:sql,QUOTA_IS_PAGES=:quota_is_pages,mark=:mark  where quota_code=:quotaCode";
			getRepository().createSqlQuery(sqlStr).setParameters(param).executeUpdate();
			map.put("result", "1");
			return map;
		}else{
			Map<String, Object> param = new HashMap<>();
			param.put("quotaCode", quotaCode);
			param.put("quotaName", quotaName);
			param.put("quotaType", quotaType);
			param.put("quotaTypeDtl", quotaTypeDtl);
			param.put("qType", qType);
			param.put("unit", unit);
			param.put("isLink",isLink);
			param.put("isQuota", isQuota);
			param.put("sql", sql);
			param.put("type", type);
			param.put("mark", mark);
			param.put("quota_is_pages", quota_is_pages);
			sqlStr = "insert into drgs.t_quotaselect(quota_code, text,type, QUOTA_TYPE,QUOTA_TYPE_DTL, q_type, unit,IS_LINK,IS_QUOTA, select_sql,QUOTA_IS_PAGES,mark) values (:quotaCode, :quotaName,:type, :quotaType,:quotaTypeDtl, :qType, :unit,:isLink,:isQuota, :sql,:quota_is_pages,:mark)";
			
			                                                                                                                                   //            {quotaType=0, quotaTypeDtl=0, isLink=0, unit=2, quotaName=999, isQuota=1, qType=ratio, quotaCode=hiWeightCntRatio, sql=99999}
			try {
				getRepository().createSqlQuery(sqlStr).setParameters(param).executeUpdate();
			}catch(Exception e) {
				e.printStackTrace();
			}
			map.put("result", "0");
			return map;
		}
	}

	@Override
	public void delQuota(String quotaCode) {
		String sql = "select 1 from drgs.t_quotaselectdtl t where quota_id='"+quotaCode+"'";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		sql = " begin \n ";
		if(list.size()>0){
			sql += "delete from drgs.t_quotaselectdtl t where t.quota_id='"+quotaCode+"'; \n ";
		}
		sql += "delete from drgs.t_quotaselect t where t.quota_code='"+quotaCode+"'; \n ";
		sql += "delete from drgs.t_quota t where t.code='"+quotaCode+"'; \n ";
		sql += "end; \n ";
		getRepository().createSqlQuery(sql).executeUpdate();
	}

	@Override
	public List<Object[]> getQuotaRelease(String quotaCode) {
		String sql = "select a.quota_code, b.text, a.indexes \n ";
		sql += " from drgs.t_quotaselectdtl a \n ";
		sql += " left join drgs.t_quotaselect b \n ";
		sql += " on a.quota_code=b.quota_code \n ";
		sql += " where a.quota_id='"+quotaCode+"' \n ";
		sql += " order by a.indexes \n ";
		List<Object[]> resultList = getRepository().createSqlQuery(sql).list();
		return resultList;
	}

	@Override
	public void delInvalidReleaseQuotas(String param, String quotaCode) {
		if(!StringUtils.hasLength(param)){
			String sql = "delete from drgs.t_quotaselectdtl where quota_id='"+quotaCode+"'";
			getRepository().createSqlQuery(sql).executeUpdate();
		}else{
			String sql = "select 1 from drgs.t_quotaselectdtl where quota_id='"+quotaCode+"' and quota_code not in ("+param+")";
			List<Object[]> list = getRepository().createSqlQuery(sql).list();
			if(list.size()>0){
				sql = "delete from drgs.t_quotaselectdtl where quota_id='"+quotaCode+"' and quota_code not in ("+param+")";
				getRepository().createSqlQuery(sql).executeUpdate();
			}
		}
	}

	@Override
	public void saveQuotaRelease(Map<String, Object> json) {
		String quotaCode = json.get("quotaCode")!=null?json.get("quotaCode").toString():"";
		String quotaName = json.get("quotaName")!=null?json.get("quotaName").toString():"";
		String quotaId = json.get("quotaId")!=null?json.get("quotaId").toString():"";
		String quotaIndex = json.get("quotaIndex")!=null?json.get("quotaIndex").toString():"";
		String sqlStr = "select 1 from drgs.t_quotaselectdtl t where t.quota_code='"+quotaCode+"' and quota_id='"+quotaId+"' ";
		List<Object> list = getRepository().createSqlQuery(sqlStr).list();
		if(list.size()>0){
			Map<String, Object> map = new HashMap<>();
			map.put("quotaCode", quotaCode);
			map.put("quotaName", quotaName);
			map.put("quotaId", quotaId);
			map.put("quotaIndex", quotaIndex);
			sqlStr = "update drgs.t_quotaselectdtl t set indexes=:quotaIndex where quota_code=:quotaCode and quota_id=:quotaId ";
			getRepository().createSqlQuery(sqlStr).setParameters(map).executeUpdate();
		}else{
			Map<String, Object> map = new HashMap<>();
			map.put("quotaCode", quotaCode);
			map.put("quotaName", quotaName);
			map.put("quotaId", quotaId);
			map.put("quotaIndex", quotaIndex);
			sqlStr = "insert into drgs.t_quotaselectdtl(quota_code, quota_desc , quota_id, indexes) values(:quotaCode, :quotaName , :quotaId, :quotaIndex) ";
			getRepository().createSqlQuery(sqlStr).setParameters(map).executeUpdate();
		}
	}
/**
 * 
 *  @Description：删除指标连同页面配置的指标一起删
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#deleteQuotaSettingByCode(java.lang.String, java.lang.String, java.lang.String, java.lang.String)
 */
	@Override
	public void deleteQuotaSettingByCode(String quotaCode, String roleCode,String pid,String id) {
		String sql = "delete from drgs.t_quota t where code='"+quotaCode+"' and role_code='"+roleCode+"' and pid='"+pid+"' and id='"+id+"'";
		getRepository().createSqlQuery(sql).executeUpdate();
	}
/**
 * 
 *  @Description：清除某角色下的所有配置
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#clearQuotaSettings(java.lang.String, java.lang.String, java.lang.String)
 */
	@Override
	public void clearQuotaSettings(String userCode, String roleCode,
			String pageCode) {
		String sql = " delete from drgs.t_quota t where 1=1 ";
		if(StringUtils.hasLength(roleCode)){
			sql += " and role_code='"+roleCode+"' ";
		}else{
			sql += " and role_code is null ";
		}
//		if(StringUtils.hasLength(userCode)){
//			sql += " and user_code='"+userCode+"' ";
//		}else{
//			sql += " and user_code is null ";
//		}
		if(StringUtils.hasLength(pageCode)){
			sql += " and page_code='"+pageCode+"' ";
		}else{
			sql += " and page_code is null ";
		}
		
		getRepository().createSqlQuery(sql).executeUpdate();
	}
	
	@Override
	public void allUpdateQuotaSql() {
		String sql="select quota_code,select_sql from  drgs.t_quotaselect ";
		List<Map<String,Object>> list=jdbcTemplateObj.queryForList(sql);
		for(Map<String,Object> map :list) {
			Map<String, Object> param = new HashMap<>();
			param.put("quotaCode", map.get("QUOTA_CODE"));
			String sqlO=map.get("SELECT_SQL")+"";
			if(sqlO.contains("-replace_t_mdc-")) {
				sqlO=sqlO.replaceAll("-replace_t_mdc-", "  left join drgs.t_mdcgroup mdc on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE ");
			}else if (sqlO.contains("-replace_t_ward-")) {
				sqlO=sqlO.replaceAll("-replace_t_ward-", "  left join drgs.t_ward ward on ipinfo.P00310001 =ward.ward_code ");
			}
			param.put("sql", sqlO);
			String sqlStr = "update drgs.t_quotaselect set select_sql=:sql where quota_code=:quotaCode";
			getRepository().createSqlQuery(sqlStr).setParameters(param).executeUpdate();
		}

	}
/**
 * 
 *  @Description：根据角色获取配置的指标
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#getQuotasByRole(java.lang.String, java.lang.String)
 */
	@Override
	public List<Object[]> getQuotasByRole(String roleCode, String userCode) {
		String sql="SELECT t.code,(case when q.mark is null then q.text else q.text||'【'||q.mark||'】' end),t.id,t.pid,t.checked, t.open, q.q_type FROM drgs.t_quota t \n"
				+ "LEFT JOIN drgs.t_quotaselect q ON t.code=q.quota_code WHERE  t.role_code='"+roleCode+"' "
				+ "and t.page_code is null  order by to_number(order_id) asc  ";
		List<Object[]> list = getRepository().createSqlQuery(sql).list();
		return list;
	}
/**
 * 
 *  @Description：获取指标添加下拉框
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#getQuotaRoleList(java.lang.String)
 */
@Override
public Object getQuotaRoleList(String quotaType) {
	String sql = "select quota_code quotaCode, (case when mark is null then text else text||'【'||mark||'】' end) quotaDesc from drgs.t_quotaselect where  1=1 ";
		   sql += "and type in('1','2') ";//查询指标，排除关联类指标	
	return jdbcTemplateObj.queryForList(sql);
	
}
/**
 *  @Description：用户界面根据用户角色获取指标(各个角色下)
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#getQuotasUserByRole(java.lang.String, java.lang.String)
 */
@Override
public List<Object[]> getQuotasUserByRole(String roleCode,String userCode, String pageCode) {
	String sqlStr="SELECT t.id,t.pid,t.code,t.role_code,t.checked,t.open,t.order_id FROM drgs.t_quota t WHERE t.role_code='"+roleCode+"' AND t.page_code IS NULL";
	List<Map<String,Object>> roleList =jdbcTemplateObj.queryForList(sqlStr);
	sqlStr="SELECT COUNT(t.id) FROM drgs.t_quota t WHERE t.role_code='"+roleCode+"' and t.user_code = '"+userCode+"'  AND t.page_code ='"+pageCode+"' ";
	int userCunt =jdbcTemplateObj.queryForObject(sqlStr,Integer.class);
	if(roleList.size()>0 && userCunt==0) {
		for(Map<String,Object> map :roleList) {
			String priId = UUID.randomUUID().toString();
			map.put("PRIID", priId);
			map.put("PAGECODE", pageCode);
			map.put("USER_CODE", userCode);
//			insert into drgs.t_quota values(:id, :pid, :code, :roleCode, :pageCode, :checked, :priId, :open,:orderId, :userCode)
			sqlStr = "insert into drgs.t_quota values(:ID, :PID, :CODE, :ROLE_CODE,:PAGECODE, :CHECKED, :PRIID, :OPEN,:ORDER_ID,:USER_CODE) ";
			getRepository().createSqlQuery(sqlStr).setParameters(map).executeUpdate();
		 }
	}else if(userCunt>roleList.size()){
		//删除多余的
		sqlStr="DELETE FROM drgs.t_quota t WHERE t.role_code='"+roleCode+"' and t.user_code = '"+userCode+"' AND t.page_code='"+pageCode+"' AND  NOT exists( "
				+ "SELECT 1 FROM drgs.t_quota m WHERE m.role_code='"+roleCode+"'  AND m.page_code IS NULL "
						+ "AND m.id||m.pid||m.role_code||m.code =t.id||t.pid||t.role_code||t.code ) ";	
		getRepository().createSqlQuery(sqlStr).executeUpdate();		
	}
	StringBuilder sql = new StringBuilder();
	sql.append(" select a.code, b.unit, a.id, a.pid, b.text, a.checked, a.open, b.q_type,  b.is_link,case when c.quota_code is not null then 1 else 0 end releate  \n ");
	sql.append(" from drgs.t_quota a  \n ");
	sql.append(" left join drgs.t_quotaselect b  \n ");
	sql.append(" on a.code=b.quota_code  \n ");
	sql.append(" left join drgs.t_quotaselect c  \n ");
	sql.append(" on a.code||'_RELEATE'=c.quota_code  \n ");
	sql.append(" where 1=1 \n ");
	sql.append(" and role_code='"+roleCode+"' ");
	sql.append(" and page_code='"+pageCode+"' ");
	sql.append(" and user_code='"+userCode+"' ");
	sql.append(" order by to_number(order_id) asc   ");
	List<Object[]> list = getRepository().createSqlQuery(sql.toString()).list();
	return list;
}
/**
 * 
 *  @Description：存储管理员下配置指标
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#adminSaveQuotas(java.util.Map)
 */
@Override
public void adminSaveQuotas(Map<String, Object> json) {
	try {
		String id = json.get("id") != null ? json.get("id").toString() : "";
		String pid = json.get("pid") != null ? json.get("pid").toString() : "";
		String code = json.get("code") != null ? json.get("code").toString() : "";
		String roleCode = json.get("roleCode") != null ? json.get("roleCode").toString() : "";
		String pageCode = json.get("pageCode") != null ? json.get("pageCode").toString() : "";
		String orderId = json.get("orderId") != null ? json.get("orderId").toString() : "";
		String userCode = json.get("userCode") != null ? json.get("userCode").toString() : "";
		String checked = "0" ;
		if(json.get("checked") != null && Boolean.parseBoolean((json.get("checked").toString()))) {
			checked =  "1" ;
		}
		json.put("checked", checked);
		String open = json.get("open") != null ? ((boolean) json.get("open") ? "1" : "0") : "";
		json.put("open", open);
		String priId = UUID.randomUUID().toString();
		json.put("priId", priId);
		Map<String, Object> param = new HashMap<>();
		param.put("id", id);
		param.put("pid", pid);
		param.put("code", code);
		param.put("roleCode", roleCode);
		param.put("userCode", userCode);
		String sql = "select t.checked,t.open from drgs.t_quota t where t.id=:id and pid=:pid and role_code=:roleCode and user_code=:userCode and code=:code  and page_code is null ";
		List<Object[]> list = getRepository().createSqlQuery(sql).setParameters(param).list();
		//判断高级管理员下有没有改指标信息
		if (list.size() == 0) {
				param = new HashMap<>();
				param.put("id", id);
				param.put("pid", pid);
				param.put("code", code);
				param.put("roleCode", roleCode);
				param.put("pageCode", pageCode);
				param.put("checked", checked);
				param.put("priId",  UUID.randomUUID().toString());
				param.put("open", open);
				param.put("orderId", orderId);
				param.put("userCode", userCode);
				sql = "insert into drgs.t_quota values(:id, :pid, :code, :roleCode, :pageCode, :checked, :priId, :open,:orderId, :userCode) ";
				getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
				// 为改角色下当前所有存在的jsp页面都添加上次质保配置
				sql = "SELECT DISTINCT t.page_code FROM drgs.t_quota t WHERE t.role_code='" + roleCode + "' AND t.page_code IS NOT NULL";
				List<String> liststr = jdbcTemplateObj.queryForList(sql, String.class);
				if (!liststr.isEmpty()) {
					for (String pagecode : liststr) {
						param.put("pageCode", pagecode);
						param.put("priId",  UUID.randomUUID().toString());
					    sql = "select t.checked,t.open from drgs.t_quota t where t.id='"+id+"' and pid='"+pid+"' and role_code='"+roleCode+"'  and code='"+code+"'  and page_code='"+pagecode+"' ";
					    list = getRepository().createSqlQuery(sql).list();
					    if(list.isEmpty()) {
							sql = "insert into drgs.t_quota t (id,pid,code,role_code,page_code,checked,pri_id,open,order_id) values(:id, :pid, :code, :roleCode, :pageCode, :checked, :priId, :open,:orderId) ";
							getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
					    }
					}
				}
		}
	}catch(Exception e) {
		e.printStackTrace();
	}
}
	/**
	 * 
	 *  @Description：页面指标更新
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.QuotaApplication#updatePagesQuotas(java.util.Map)
	 */
	@Override
	public void updatePagesQuotas(Map<String, Object> json) {
		try {
			String id = json.get("id") != null ? json.get("id").toString() : "";
			String pid = json.get("pid") != null ? json.get("pid").toString() : "";
			String code = json.get("code") != null ? json.get("code").toString() : "";
			String roleCode = json.get("roleCode") != null ? json.get("roleCode").toString() : "";
			String pageCode = json.get("pageCode") != null ? json.get("pageCode").toString() : "";
			String checked = json.get("checked") != null ? ((boolean) json.get("checked") ? "1" : "0") : "";
			String open = json.get("open") != null ? ((boolean) json.get("open") ? "1" : "0") : "";
			String userCode = json.get("userCode") != null ? json.get("userCode").toString() : "";
			Map<String, Object> param = new HashMap<>();
			String sql  = "select t.checked,t.open from drgs.t_quota t where t.id='"+id+"' and pid='"+pid+"' and role_code='"+roleCode+"'  and code='"+code+"'  and page_code='"+pageCode+"' and user_code='"+userCode+"' ";
			List<Object[]> list = getRepository().createSqlQuery(sql).list();
			if (list.size() > 0) {
				// 选中和是否打开有一个不一样，则进行更新
				if (!checked.equals(list.get(0)[0].toString()) || !open.equals(list.get(0)[1].toString())) {
					param = new HashMap<>();
					param.put("id", id);
					param.put("pid", pid);
					param.put("code", code);
					param.put("roleCode", roleCode);
					param.put("pageCode", pageCode);
					param.put("checked", checked);
					param.put("open", open);
					param.put("userCode", userCode);
					sql = "update drgs.t_quota t set checked=:checked, open=:open where role_code=:roleCode and code=:code and id=:id and  pid=:pid and page_code=:pageCode and user_code=:userCode";
					getRepository().createSqlQuery(sql).setParameters(param).executeUpdate();
				}
			} 
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}
